package com.luych.toolbox.data.service.core.dm;

import com.luych.toolbox.common.enums.DataSourceType;
import com.luych.toolbox.data.service.core.CoreConnection;
import com.luych.toolbox.data.service.core.CoreDatabase;
import dm.jdbc.driver.DmDriver;
import dm.jdbc.driver.DmdbDataSource;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.boot.jdbc.DataSourceBuilder;
import org.springframework.jdbc.core.JdbcTemplate;

import javax.sql.DataSource;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;
import java.util.Objects;

public class DmConnection implements CoreConnection {

    private static final Logger LOGGER = LoggerFactory.getLogger(DmConnection.class);

    private final String db;

    private final DataSource dataSource;
    private final JdbcTemplate jdbcTemplate;

    public DmConnection(String host, int port, String user, String pass, String db) {
        String url = String.format("jdbc:dm://%s:%d/%s", host, port, db);
        this.dataSource = DataSourceBuilder.create()
                .type(DmdbDataSource.class)
                .driverClassName(DmDriver.class.getName())
                .url(url).username(user).password(pass)
                .build();
        this.jdbcTemplate = new JdbcTemplate(dataSource);
        this.db = db;
    }

    private List<DmTable> getTables(String database) {
        LOGGER.info("Get [" + database + "] Tables ...");
        List<Map<String, Object>> tableMaps = jdbcTemplate.queryForList(
                "SELECT A.TABLE_NAME, B.COMMENTS FROM USER_TABLES A " +
                        "LEFT JOIN USER_TAB_COMMENTS B ON A.TABLE_NAME = B.TABLE_NAME " +
                        "ORDER BY A.TABLE_NAME"
        );
        LOGGER.info("Get [" + database + "] Columns ...");
        List<Map<String, Object>> columnMaps = jdbcTemplate.queryForList(
                "SELECT A.TABLE_NAME, A.COLUMN_NAME, A.DATA_TYPE, '' AS COLUMN_TYPE, A.NULLABLE, " +
                        "B.COMMENTS, A.DATA_LENGTH, A.DATA_PRECISION, A.DATA_SCALE," +
                        "DECODE(D.INFO2, 1, 'Y', 'N') INCRE " +
                        "FROM USER_TAB_COLUMNS A " +
                        "LEFT JOIN USER_COL_COMMENTS B ON A.COLUMN_NAME = B.COLUMN_NAME AND A.TABLE_NAME = B.TABLE_NAME " +
                        "LEFT JOIN USER_OBJECTS C ON A.TABLE_NAME = C.OBJECT_NAME " +
                        "LEFT JOIN SYSCOLUMNS D ON C.OBJECT_ID = D.ID AND A.COLUMN_NAME = D.NAME " +
                        "ORDER BY A.TABLE_NAME, A.COLUMN_NAME"
        );
        LOGGER.info("Get [" + database + "] PrimaryKeys ...");
        List<Map<String, Object>> primaryKeyMaps = jdbcTemplate.queryForList(
                "SELECT A.TABLE_NAME, B.COLUMN_NAME, B.POSITION, A.CONSTRAINT_NAME " +
                        "FROM USER_CONSTRAINTS A " +
                        "JOIN USER_CONS_COLUMNS B ON A.CONSTRAINT_NAME = B.CONSTRAINT_NAME AND A.TABLE_NAME = B.TABLE_NAME " +
                        "WHERE A.CONSTRAINT_TYPE = 'P' " +
                        "ORDER BY A.TABLE_NAME, B.POSITION;"
        );

        List<DmTable> tables = new ArrayList<>();
        for (Map<String, Object> tableMap : tableMaps) {
            String tableName = Objects.toString(tableMap.get("TABLE_NAME"), "");
            String tableComment = Objects.toString(tableMap.get("COMMENTS"), "");
            List<DmColumn> columns = new ArrayList<>();
            for (Map<String, Object> columnMap : columnMaps) {
                if (tableName.equals(Objects.toString(columnMap.get("TABLE_NAME"), ""))) {
                    String columnName = Objects.toString(columnMap.get("COLUMN_NAME"), "");
                    String columnDataType = Objects.toString(columnMap.get("DATA_TYPE"), "");
                    String columnNullable = Objects.toString(columnMap.get("NULLABLE"), "");
                    String columnComment = Objects.toString(columnMap.get("COMMENTS"), "");
                    String columnCharLength = Objects.toString(columnMap.get("DATA_LENGTH"), null);
                    String columnNumericPrecision = Objects.toString(columnMap.get("DATA_PRECISION"), null);
                    String columnNumericScale = Objects.toString(columnMap.get("DATA_SCALE"), null);
                    String columnIncrement = Objects.toString(columnMap.get("INCRE"), "");

                    if ("DOUBLE PRECISION".equals(columnDataType)) {
                        columnDataType = "DOUBLE";
                    }

                    DmDataType dmDataType = DmDataType.valueOf(columnDataType.toUpperCase());

                    DmColumn column = new DmColumn(
                            tableName, columnName, dmDataType,
                            columnCharLength == null ? null : Long.parseLong(columnCharLength),
                            columnNumericPrecision == null ? null : Long.parseLong(columnNumericPrecision),
                            columnNumericScale == null ? null : Long.parseLong(columnNumericScale),
                            "Y".equals(columnNullable),
                            columnComment,
                            "Y".equals(columnIncrement)
                    );
                    columns.add(column);
                }
            }
            String primaryKeyName = null;
            List<String> primaryKeys = new ArrayList<>();
            for (Map<String, Object> primaryKeyMap : primaryKeyMaps) {
                if (tableName.equals(Objects.toString(primaryKeyMap.get("TABLE_NAME"), ""))) {
                    primaryKeys.add(Objects.toString(primaryKeyMap.get("COLUMN_NAME"), ""));
                    if (primaryKeyName == null && primaryKeyMap.containsKey("CONSTRAINT_NAME")) {
                        primaryKeyName = Objects.toString(primaryKeyMap.get("CONSTRAINT_NAME"));
                    }
                }
            }
            DmTable table = new DmTable(tableName, tableComment, columns, primaryKeyName, primaryKeys);
            tables.add(table);
        }
        return tables;
    }

    private List<DmView> getViews(String database) {
        LOGGER.info("Get [" + database + "] Views ...");
        List<Map<String, Object>> viewMaps = jdbcTemplate.queryForList(
                "SELECT A.VIEW_NAME FROM USER_VIEWS A ORDER BY A.VIEW_NAME"
        );
        List<DmView> views = new ArrayList<>();
        for (Map<String, Object> viewMap : viewMaps) {
            String viewName = Objects.toString(viewMap.get("VIEW_NAME"), "");
            views.add(new DmView(viewName));
        }
        return views;
    }

    @Override
    public DataSourceType getDataSourceType() {
        return DataSourceType.DM;
    }

    @Override
    public CoreDatabase getDatabase() {
        return new DmDatabase(db, this.getTables(db), this.getViews(db));
    }

    @Override
    public DataSource getDataSource() {
        return this.dataSource;
    }
}
